# 利用 R 操作 MySQL -- 提取符合要求的连续日期数据

library(DBI)

conf_file = file.path(Sys.getenv('USERPROFILE'), '.r-dbi')
database = 'local-mysql'

con = dbConnect(RMariaDB::MariaDB(), default.file=conf_file, group=database)

sqlQuery = function(conn, sql) {
    query = dbSendQuery(con, sql)
    res = dbFetch(query)
    dbClearResult(query)
    res
}

sql_raw = "SELECT * FROM tmp_temperature"

sql = "
WITH T AS (
    SELECT *,
           DATEDIFF(date, '2000-01-01') - (ROW_NUMBER() OVER(PARTITION BY mcode ORDER BY date)) diff
    FROM (
        SELECT *
        FROM tmp_temperature
    ) _
)
SELECT mcode, longtitude, latitude, date, diff FROM T
WHERE diff IN (SELECT diff FROM T GROUP BY mcode, diff HAVING COUNT(*) >= 3)
ORDER BY mcode, date
"

sqlQuery(con, sql_raw)
sqlQuery(con, sql)

dbDisconnect(con)

## temperature.csv
# mcode,longtitude,latitude,date
# 620302,102.1492,38.65512,6/3/2013
# 620302,102.1492,38.65512,6/4/2013
# 620302,102.1492,38.65512,6/5/2013
# 620302,102.1492,38.65512,6/7/2013
# 620302,102.1492,38.65512,6/27/2013
# 620302,102.1492,38.65512,6/29/2013
# 620302,102.1492,38.65512,7/2/2013
# 620302,102.1492,38.65512,7/6/2013
# 620302,102.1492,38.65512,7/21/2013
# 620302,102.1492,38.65512,7/22/2013
# 620302,102.1492,38.65512,7/23/2013
# 620302,102.1492,38.65512,7/29/2013
# 620302,102.1492,38.65512,7/30/2013
# 620302,102.1492,38.65512,8/3/2013
# 620302,102.1492,38.65512,8/4/2013
# 620302,102.1492,38.65512,8/9/2013
# 620302,102.1492,38.65512,8/12/2013
# 620302,102.1492,38.65512,8/13/2013
# 620302,102.1492,38.65512,8/14/2013
# 653001,76.85818,40.12271,6/11/2013
# 653001,76.85818,40.12271,6/12/2013
# 653001,76.85818,40.12271,7/28/2013
# 653001,76.85818,40.12271,7/29/2013
# 653001,76.85818,40.12271,7/30/2013
# 653001,76.85818,40.12271,7/31/2013
# 653001,76.85818,40.12271,8/1/2013
# 653001,76.85818,40.12271,8/2/2013
# 653001,76.85818,40.12271,8/3/2013
# 653001,76.85818,40.12271,8/4/2013
# 653001,76.85818,40.12271,8/7/2013
